package com.cloudcc.migrationToNewSys;

import com.cloudcc.ccTool.CCObject;
import com.cloudcc.ccTool.CCService;
import com.cloudcc.ccTool.UserInfo;
import net.sf.json.JSONObject;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static cn.hutool.poi.excel.cell.CellUtil.getCellValue;

public class ImporUser {
    public static void main(String[] args) throws Exception {
        UserInfo UserInfo = new UserInfo();
        CCService cs = new CCService(UserInfo);
        File realfile = new File("e:\\桌面\\新建用户1.xlsx");
        // 使用文件初始化Workbook
        Workbook wb = WorkbookFactory.create(realfile);

        Sheet sheet1 = wb.getSheetAt(0);
        /*********************** 初始化完毕 ****************************/
        ArrayList<Map<String, String>> jiluList = new ArrayList<Map<String, String>>();
        int count = 0;//记录条数（表中第几行）
        for (Row row : sheet1) {
            count++;
            if (count == 1) {
                //读取表头信息
                Cell cell1 = row.getCell(0);
                String t1 = (String) getCellValue(cell1);
            } else {
                //读取列数
                int lastColumn = row.getLastCellNum();
                //一次获取每列内容
                String l1 = getStringCellValue(row, 0);
                String l2 = getStringCellValue(row, 1);
                String l3 = getStringCellValue(row, 2);
                String l4 = getStringCellValue(row, 3);
                String l5 = getStringCellValue(row, 4);
                String l6 = getStringCellValue(row, 5);
                String l7 = getStringCellValue(row, 6);
                String l8 = getStringCellValue(row, 7);

                if (count > 1) {
                    //将读取的记录添加到集合中以便判断记录的有效性
                    HashMap<String, String> onejilu = new HashMap<String, String>();
                    onejilu.put("username", l1);
                    onejilu.put("loginname", l2);
                    onejilu.put("employeenum", l3);
                    onejilu.put("phone", l4);
                    onejilu.put("email", l5);
                    onejilu.put("bm", l6);
                    onejilu.put("role", l7);
                    onejilu.put("profile", l8);
                    jiluList.add(onejilu);
                }
            }
        }
        System.out.println("jiluList = " + jiluList);
        for (int i = 0; i < jiluList.size(); i++) {
            String username = jiluList.get(i).get("username") + "";
            String loginname = jiluList.get(i).get("loginname") + "";
            String employeenum = jiluList.get(i).get("employeenum") + "";
            String phone = jiluList.get(i).get("phone") + "";
            String email = jiluList.get(i).get("email") + "";
            String bm = jiluList.get(i).get("bm") + "";
            String role = jiluList.get(i).get("role") + "";
            String profile = jiluList.get(i).get("profile") + "";

            //间档查询（根据名称）
            List<CCObject> profileList = cs.cqlQuery("tp_sys_profile", "select * from tp_sys_profile where profilename='" + profile + "'");
            String profileID = "";
            if (profileList.size() > 0) {
                profileID = profileList.get(0).get("id").toString();//简档ID
                System.out.println("姓名=" + username + "----简档ID=" + profileID);
            } else {
                System.out.println("姓名=" + username + "----简档=" + profile);
            }
            //角色查询（根据名称）
            List<CCObject> roleList = cs.cqlQuery("role", "select * from role where rolename='" + role + "'");
            String roleID = "";
            if (roleList.size() > 0) {
                roleID = roleList.get(0).get("roleid").toString();//角色ID
                System.out.println("姓名=" + username + "----角色ID=" + roleID);
            } else {
                System.out.println("姓名=" + username + "----角色=" + role);
            }
            List<CCObject> bmList = cs.cqlQuery("bmzz", "select id from bmzz where name='" + bm + "'");
            String bmID = "";
            if (bmList.size() > 0) {
                bmID = bmList.get(0).get("id").toString();//角色ID
                System.out.println("姓名=" + username + "----部门ID=" + bmID);
            } else {
                System.out.println("姓名=" + username + "----部门=" + bm);
            }

            List<CCObject> userList = cs.cquery("ccuser", "email='" + email + "'");//用户查询，根据员工编号

            if (userList.size() == 0) {
                // 导入用户数据
                //获取binding
                String orgApiUrl = cs.getOrgApiUrl();
                String url=orgApiUrl+"/openApi/common";
                JSONObject par = new JSONObject();
                par.put("serviceName", "createUser");
                par.put("loginName", loginname);
                par.put("name", username);
                par.put("email", email);
                par.put("profileId", profileID);
                par.put("roleId", roleID);
//                par.put("password", "");
                par.put("sendEmail", 0);
                String relStr= cs.CCPost(url, par.toString());
                System.out.println("relStr = " + relStr);
                JSONObject jsonObject = JSONObject.fromObject(relStr);
                if (jsonObject.getString("result").equals("true")) {
                    List<CCObject> user = cs.cquery("ccuser", "email='" + email + "'");
                    if (user.size() > 0) {
                        CCObject cco = user.get(0);
                        cco.put("employeenum",employeenum);
                        cco.put("mobile",phone);
                        cco.put("bmzz",bmID);
                        cs.updateLt(cco);
                    }
                }

            } else {
                CCObject cco = userList.get(0);
                cco.put("employeenum",employeenum);
                cco.put("mobile",phone);
                cco.put("bmzz",bmID);
                cs.updateLt(cco);
                System.out.println("用户："+username+"已存在");
            }
        }
    }


    private static String getStringCellValue(Row row, int cellIndex) {
        Cell cell = row.getCell(cellIndex);
        return getCellValue(cell) == null ? "" : getCellValue(cell).toString().trim();
    }
}
